﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AutoMapper;
using System.ComponentModel.Composition;
using System.IO;
using System.Data;
using System.Diagnostics.CodeAnalysis;
using System.Data.SqlClient;
using System.Data.EntityClient;
using System.Globalization;

namespace Steve.EFDataLayer
{
    [Export(typeof(ContentRepository))]
    [PartCreationPolicy(CreationPolicy.Shared)]
    public class EFContentRepository : ContentRepository
    {
        static EFContentRepository()
        {
            SteveEFDataLayer.SetupAutoMapper();
        }

        public override Content GetContent(Guid id, int? revision, bool approvedOnly)
        {
            Func<DbContent, bool> whereClause;

            if (revision == null)
            {
                if (approvedOnly)
                    whereClause = c => c.LatestApprovedRevisionNumber == c.RevisionNumber;
                else
                    whereClause = c => c.LatestRevisionNumber == c.RevisionNumber;
            }
            else
            {
                whereClause = c => c.RevisionNumber == revision.Value && (!approvedOnly || c.ApprovalDate != null);
            }

            using (var ctx = new SteveEntities())
            {
                var query = from c in ctx.DbContents.Where(whereClause)
                            where c.Id == id
                            select c;

                var db = query.FirstOrDefault();

                if (db == null) return null;

                return Mapper.Map<DbContent, Content>(db);
            }
        }

        public override Stream GetContentData(Guid id)
        {
            using (var ctx = new SteveEntities())
            {
                var query = from cd in ctx.DbContentDatas
                            where cd.Id == id
                            select cd;

                var db = query.FirstOrDefault();

                if (db == null) return null;

                return new MemoryStream(db.BinaryData);
            }
        }

        protected override IEnumerable<Tuple<string, Guid>> GetContentPaths()
        {
            using (var ctx = new SteveEntities())
            {
                var query = from c in ctx.DbContents
                            orderby c.Path ascending
                            select new { c.Path, c.Id };

                return query.ToList().Select(c => Tuple.Create(c.Path, c.Id));
            }
        }

        public override IEnumerable<Tuple<Content, float>> GetContentSimilarToContent(Guid similarToContentId, bool approvedOnly, int count, string onlyContentTypeName, Guid? unreadByMembershipId)
        {
            if (unreadByMembershipId != null) throw new NotImplementedException();

            using (var ctx = new SteveEntities())
            {
                var query = (from sim in ctx.DbContentSimilarityCaches
                             where sim.CategorySimilarity != null && sim.CategorySimilarity > 0 && sim.ContentId == similarToContentId
                             join c in ctx.DbContents on sim.SimilarContentId equals c.Id
                             where c.RevisionNumber == c.LatestRevisionNumber 
                                && (!approvedOnly || c.ApprovalDate != null) 
                                && (onlyContentTypeName == null || c.ContentTypeName == onlyContentTypeName)
                             orderby sim.CategorySimilarity descending
                             select new { Content = c, sim.CategorySimilarity }).Take(count);

                return query.ToList().Select(db => Tuple.Create(Mapper.Map<DbContent, Content>(db.Content), (float)db.CategorySimilarity)).ToList();
            }
        }

        [SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity")]
        [SuppressMessage("Microsoft.Design", "CA1062:Validate arguments of public methods", MessageId = "1")]
        [SuppressMessage("Microsoft.Design", "CA1062:Validate arguments of public methods", MessageId = "0")]
        [SuppressMessage("Microsoft.Maintainability", "CA1506:AvoidExcessiveClassCoupling")]
        public override IEnumerable<Tuple<Content, float?>> GetContentByCategories(IEnumerable<string> requiredCategoryNames, IEnumerable<string> optionalCategoryNames, bool includeRelevancy, bool approvedOnly, int count, Guid? excludeContentId, string onlyContentTypeName, Guid? unreadByMembershipId)
        {
            if (unreadByMembershipId != null) throw new NotImplementedException();

            if (requiredCategoryNames == null && optionalCategoryNames == null) throw new ArgumentNullException(null, "You must pass either requiredCategoryNames or optionalCategoryNames or both");

            var hasRequired = requiredCategoryNames != null && requiredCategoryNames.Any();
            var hasOptional = optionalCategoryNames != null && optionalCategoryNames.Any();

            using (var requiredDt = new DataTable())
            {
                requiredDt.Locale = CultureInfo.InvariantCulture;

                using (var optionalDt = new DataTable())
                {
                    optionalDt.Locale = CultureInfo.InvariantCulture;

                    requiredDt.Columns.Add("CategoryName", typeof(string));
                    optionalDt.Columns.Add("CategoryName", typeof(string));

                    var returnValue = new List<Tuple<Content, float?>>();

                    if (hasRequired)
                    {
                        foreach (var name in requiredCategoryNames)
                        {
                            requiredDt.Rows.Add(name);
                        }
                    }

                    if (hasOptional)
                    {
                        foreach (var name in optionalCategoryNames)
                        {
                            optionalDt.Rows.Add(name);
                        }
                    }

                    //TODO: this is a little hacky since I can't use a TVP in an entity stored proc, should it be cleaned up?
                    string connString;
                    using (var ctx = new SteveEntities())
                    {
                        connString = ((EntityConnection)ctx.Connection).StoreConnection.ConnectionString;

                        using (var conn = new SqlConnection(connString))
                        {
                            conn.Open();
                            var cmd = conn.CreateCommand();
                            cmd.CommandType = CommandType.StoredProcedure;

                            cmd.Parameters.AddWithValue("@Top", count);
                            cmd.Parameters.AddWithValue("@ApprovedOnly", approvedOnly);
                            cmd.Parameters.AddWithValue("@ExcludeContentId", excludeContentId == null ? (object)DBNull.Value : excludeContentId.Value);
                            cmd.Parameters.AddWithValue("@ContentTypeName", string.IsNullOrWhiteSpace(onlyContentTypeName) ? (object)DBNull.Value : onlyContentTypeName);

                            if ((hasRequired && hasOptional) || (hasOptional && includeRelevancy))
                            {
                                cmd.CommandText = "steve.spSteve_GetSimilarContentAdHoc";

                                var param = cmd.Parameters.AddWithValue("@RequiredCategoryNames", requiredDt);
                                param.SqlDbType = SqlDbType.Structured;

                                param = cmd.Parameters.AddWithValue("@OptionalCategoryNames", optionalDt);
                                param.SqlDbType = SqlDbType.Structured;
                            }
                            else if (hasRequired)
                            {
                                cmd.CommandText = "steve.spSteve_GetContentByAllCategories";

                                var param = cmd.Parameters.AddWithValue("@RequiredCategoryNames", requiredDt);
                                param.SqlDbType = SqlDbType.Structured;
                            }
                            else if (hasOptional && !includeRelevancy)
                            {
                                cmd.CommandText = "steve.spSteve_GetContentByAnyCategory";

                                var param = cmd.Parameters.AddWithValue("@OptionalCategoryNames", optionalDt);
                                param.SqlDbType = SqlDbType.Structured;
                            }
                            else
                            {
                                throw new ArgumentException("Invalid combination of parameters.");
                            }

                            using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                            {
                                while (dr.Read())
                                {
                                    var c = Mapper.Map<IDataRecord, Content>(dr);
                                    var ordinal = dr.GetOrdinal("CategorySimilarity");

                                    returnValue.Add(Tuple.Create(c, dr.IsDBNull(ordinal) ? (float?)null : dr.GetFloat(ordinal)));
                                }
                            }
                        }
                    }

                    return returnValue;
                }
            }
        }

        public override IEnumerable<string> GetContentCategories(Guid contentId)
        {
            using (var ctx = new SteveEntities())
            {
                var query = from cat in ctx.DbContentCategories
                            where cat.ContentId == contentId && cat.DeleteDate == null
                            select cat.CategoryName;

                return query.ToList();
            }
        }


        public override Content GetContentForSiteUrl(Guid siteUrlId, bool approvedOnly)
        {
            Func<DbContent, bool> whereClause;

            if (approvedOnly)
                whereClause = c => c.LatestApprovedRevisionNumber == c.RevisionNumber;
            else
                whereClause = c => c.LatestRevisionNumber == c.RevisionNumber;

            using (var ctx = new SteveEntities())
            {
                var query = from c in ctx.DbContents.Where(whereClause)
                            where c.SiteUrlId == siteUrlId
                            select c;

                var db = query.FirstOrDefault();

                if (db == null) return null;

                return Mapper.Map<DbContent, Content>(db);
            }
        }

        public override IEnumerable<Tuple<Guid, int>> GetUpdatedContentIds(DateTime? updatedSince)
        {
            using (var ctx = new SteveEntities())
            {
                var query = from c in ctx.DbContents
                            where updatedSince == null || c.RevisionDate >= updatedSince.Value
                            select new { c.Id, c.RevisionNumber };

                return query.ToList().Select(r => Tuple.Create(r.Id, r.RevisionNumber));
            }
        }
    }
}
